package com.x.erp.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.x.erp.model.vo.DetectionVO;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.x.erp.model.Detection;
import com.x.framework.dao.BaseDao;
@Repository
public class DetectionDao extends BaseDao {

	private final static String selectDetectionListSql = "SELECT " + 
			"ed.*, " +
			"em.MARKET_NAME, " +
			"ec.CAR_TITLE, " + 
			"ec.CAR_VIN, " + 
			"ec.CAR_LICENCE_DATE, " + 
			"ec.CAR_PLATE, " + 
			"ec.CAR_MILEAGE, " + 
			"ec.CAR_IMAGE_URL, " +
			"bb.BRAND_NAME, " +
			"es.SHOP_NAME, " + 
			"es.SHOP_MOBILE, " + 
			"es.SHOP_ADDRESS, " + 
			"es.SHOP_LEGAL_NAME, " + 
			"es.SHOP_LEGAL_MOBILE, " + 
			"es.SHOP_PARK_NUM, " + 
			"es.SHOP_CAR_NUM, " + 
			"eu.USER_NAME, " + 
			"eu.USER_MOBILE " +
			"FROM " +
			"ERP_DETECTION ed " + 
			"JOIN ERP_CAR ec on ed.CAR_ID = ec.CAR_ID " + 
			"JOIN ERP_MARKET em ON ed.MARKET_ID = em.MARKET_ID " +
			"JOIN ERP_MARKET emp ON emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') " + 
			"JOIN x_base.BASE_BRAND bb ON ec.BRAND_ID = bb.BRAND_ID " +
			"JOIN ERP_SHOP es ON ec.SHOP_ID = es.SHOP_ID " + 
			"LEFT JOIN ERP_USER eu ON eu.USER_ID = ed.DETECT_USER_ID " + 
			"WHERE true ";
	/**
	 * 查询检测单信息列表
	 * @param detection
	 * @return
	 * @throws Exception 
	 */
	public List<Detection> selectDetectionList(Detection detection) throws Exception{
		StringBuilder sql = new StringBuilder(selectDetectionListSql);
		List<Object> params = new ArrayList<Object>();
		params.add(detection.getMarketId());
		searchCondition(sql, params, detection);
		sql.append("ORDER BY ed.DETECTION_STATE ASC , ed.DETECTION_CREATE_TIME  ASC ");
		return this.queryForList(sql.toString(), Detection.class, params, detection);
	}

	private final static String selectDetectionNumSql = "SELECT " + 
			"COUNT(ed.DETECTION_ID) " +
			"FROM " + 
			"ERP_DETECTION ed " + 
			"JOIN ERP_CAR ec on ed.CAR_ID = ec.CAR_ID " + 
			"JOIN ERP_MARKET em ON ed.MARKET_ID = em.MARKET_ID " +
			"JOIN ERP_MARKET emp ON emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') " + 
			"JOIN x_base.BASE_BRAND bb ON ec.BRAND_ID = bb.BRAND_ID " +
			"JOIN ERP_SHOP es ON ec.SHOP_ID = es.SHOP_ID " +
			"LEFT JOIN ERP_USER eu ON eu.USER_ID = ed.DETECT_USER_ID " +
			"WHERE true ";
	/**
	 * 查询检测单信息数量
	 * @param detection
	 * @return
	 */
	public int selectDetectionNum(Detection detection) {
		StringBuilder sql = new StringBuilder(selectDetectionNumSql);
		List<Object> params = new ArrayList<Object>();
		params.add(detection.getMarketId());
		searchCondition(sql, params, detection);
		return this.queryForInt(sql.toString(), params);
	}

	/**
	 * 查询条件
	 * @param sql Sql
	 * @param detection 查询对象
	 * @param params 参数
	 */
	private void searchCondition(StringBuilder sql, List<Object> params, Detection detection) {
		if(detection != null) {
			if(this.isString(detection.getDetectionId())){
				sql.append("AND ed.DETECTION_ID = ? ");
				params.add(detection.getDetectionId());
			}
			if(this.isNotNull(detection.getUser()) && this.isString(detection.getUser().getUserName())){
				sql.append("AND eu.USER_NAME LIKE ? ");
				params.add(detection.getUser().getUserName()+"%");
			}
			if(this.isNotNull(detection.getUser()) && this.isString(detection.getUser().getUserMobile())){
				sql.append("AND eu.USER_MOBILE = ? ");
				params.add(detection.getUser().getUserMobile());
			}
			if(this.isNotNull(detection.getDetectionNo())){
				sql.append("AND ed.DETECTION_NO = ? ");
				params.add(detection.getDetectionNo());
			}
			if(this.isString(detection.getDetectUserId())){
				sql.append("AND ed.DETECT_USER_ID = ? ");
				params.add(detection.getDetectUserId());
			}
			if(this.isString(detection.getCarId())){
				sql.append("AND ed.CAR_ID = ? ");
				params.add(detection.getCarId());
			}
			if(this.isNotNull(detection.getCar()) && this.isString(detection.getCar().getShopId())){
				sql.append("AND es.SHOP_ID = ? ");
				params.add(detection.getCar().getShopId());
			}
			if(detection.getDetectionState() != null){
				sql.append("AND ed.DETECTION_STATE = ? ");
				params.add(detection.getDetectionState());
			}
			if(detection.getDetectionVO() != null) {
				DetectionVO detectionVO = detection.getDetectionVO();
				if (detectionVO.getApplyStartDate() != null) {
					sql.append("AND ed.DETECTION_CREATE_TIME > ? ");
					params.add(detectionVO.getApplyStartDate());
				}
				if (detectionVO.getApplyEndDate() != null) {
					sql.append("AND ed.DETECTION_CREATE_TIME < ? ");
					params.add(detectionVO.getApplyEndDate());
				}
			}
		}
	}

	private final static String selectDetectionSql = "SELECT " + 
			"ed.*, " +
			"em.MARKET_NAME, " +
			"ec.CAR_TITLE, " + 
			"ec.CAR_VIN, " + 
			"ec.CAR_LICENCE_DATE, " + 
			"ec.CAR_PLATE, " + 
			"bb.BRAND_NAME, " + 
			"es.SHOP_ID, " + 
			"es.SHOP_NAME, " + 
			"es.SHOP_MOBILE, " + 
			"es.SHOP_ADDRESS, " +
			"es.SHOP_LEGAL_NAME, " +
			"es.SHOP_LEGAL_MOBILE, " +
			"eu.USER_NAME, " + 
			"eu.USER_MOBILE, " + 
			"em.MARKET_CODE "+
			"FROM " + 
			"ERP_DETECTION ed " + 
			"JOIN ERP_CAR ec on ed.CAR_ID = ec.CAR_ID " + 
			"JOIN x_base.BASE_BRAND bb ON ec.BRAND_ID = bb.BRAND_ID " +
			"JOIN ERP_SHOP es ON ec.SHOP_ID = es.SHOP_ID " + 
			"JOIN ERP_MARKET em ON em.MARKET_ID = es.MARKET_ID "+
			"LEFT JOIN ERP_USER eu ON eu.USER_ID = ed.DETECT_USER_ID " + 
			"WHERE true ";
	/**
	 * 查询检测单信息
	 * @param detection
	 * @return
	 * @throws Exception 
	 */
	public Detection selectDetection(Detection detection) throws Exception {
		StringBuilder sql = new StringBuilder(selectDetectionSql);
		List<Object> params = new ArrayList<Object>();
		if(this.isString(detection.getDetectionId())){
			sql.append("AND ed.DETECTION_ID = ? ");
			params.add(detection.getDetectionId());
		}
		if(this.isString(detection.getCarId())){
			sql.append("AND ed.CAR_ID = ? ");
			params.add(detection.getCarId());
		}
		if(detection.getDetectionState() != null){
			sql.append("AND ed.DETECTION_STATE = ? ");
			params.add(detection.getDetectionState());
		}
		List<Detection> detections = this.queryForList(sql.toString(), Detection.class, params);
		if(detections != null && detections.size() > 0) {
			return detections.get(0);
		}
		return null;
	}
	
	private final static String selectCurrentNumSql = "SELECT " + 
			"MAX(ed.DETECTION_NO) maxDetectNo " +
			"FROM " + 
			"ERP_DETECTION ed " + 
			"JOIN ERP_CAR ec on ed.CAR_ID = ec.CAR_ID " + 
			"JOIN ERP_MARKET em ON ed.MARKET_ID = em.MARKET_ID " +
			"WHERE em.MARKET_ID = ? AND ed.DETECTION_NO LIKE ? ";
	/**
	 * 通过marketID查询当前应该录入的检测单编号序号（当天总数+1）
	 * @param detection
	 * @return
	 * @throws Exception 
	 */
	public int selectCurrentNum(Detection detection,String detectionCode) {
		StringBuilder sql = new StringBuilder(selectCurrentNumSql);
		List<Object> params = new ArrayList<Object>();
		params.add(detection.getMarketId());
		params.add(detectionCode);
		RowMapper<Map<String, String>> mapper = new RowMapper<Map<String, String>>() {
			@Override
			public Map<String, String> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, String> map = new HashMap<> ();
				map.put("maxDetectNo", rs.getString ("maxDetectNo"));
				return map;
			}
		};
		String maxDetectNo = this.queryForList(sql.toString(), mapper, params).get(0).get ("maxDetectNo");
		if(StringUtils.isEmpty (maxDetectNo)){
			return 1;
		}
		// 截取后三位的序号
		Integer tem = Integer.valueOf (maxDetectNo.substring (maxDetectNo.length () - 3));
		return tem.intValue () + 1;
	}
}
